import 'package:drift/drift.dart';
import 'database_adapter.dart';

part 'app_database.g.dart';

@DataClassName('User')
class Users extends Table {
  IntColumn get id => integer().autoIncrement()();
  TextColumn get username => text()();
  TextColumn get email => text()();
  TextColumn get avatar => text().nullable()();
  TextColumn get role => text()();
  BoolColumn get isActive => boolean().withDefault(Constant(true))();
  DateTimeColumn get createTime =>
      dateTime().withDefault(Constant(DateTime.now()))();
  DateTimeColumn get updateTime =>
      dateTime().withDefault(Constant(DateTime.now()))();
}

@DataClassName('Project')
class Projects extends Table {
  IntColumn get id => integer().autoIncrement()();
  TextColumn get projectName => text()();
  TextColumn get description => text().nullable()();
  TextColumn get status => text()();
  IntColumn get ownerId => integer()();
  DateTimeColumn get createTime =>
      dateTime().withDefault(Constant(DateTime.now()))();
  DateTimeColumn get updateTime =>
      dateTime().withDefault(Constant(DateTime.now()))();
}

@DataClassName('Todo')
class Todos extends Table {
  IntColumn get id => integer().autoIncrement()();
  TextColumn get title => text()();
  TextColumn get description => text().nullable()();
  TextColumn get status => text()();
  IntColumn get projectId => integer()();
  IntColumn get assigneeId => integer().nullable()();
  IntColumn get reporterId => integer()();
  IntColumn get estimatedHours => integer().nullable()();
  IntColumn get actualHours => integer().nullable()();
  DateTimeColumn get createTime =>
      dateTime().withDefault(Constant(DateTime.now()))();
  DateTimeColumn get updateTime =>
      dateTime().withDefault(Constant(DateTime.now()))();
  DateTimeColumn get dueDate => dateTime().nullable()();
}

// 添加笔记表
@DataClassName('Note')
class Notes extends Table {
  IntColumn get id => integer().autoIncrement()();
  IntColumn get projectId => integer().nullable()();
  IntColumn get todoId => integer().nullable()();
  TextColumn get title => text()();
  TextColumn get content => text()();
  TextColumn get noteType => text().withDefault(Constant('GENERAL'))();
  TextColumn get tags => text().nullable()();
  IntColumn get authorId => integer()();
  BoolColumn get isPublic => boolean().withDefault(Constant(false))();
  DateTimeColumn get createTime =>
      dateTime().withDefault(Constant(DateTime.now()))();
  DateTimeColumn get updateTime =>
      dateTime().withDefault(Constant(DateTime.now()))();
}

@DriftDatabase(tables: [Users, Projects, Todos, Notes])
class AppDatabase extends _$AppDatabase {
  AppDatabase() : super(_openConnection());

  @override
  int get schemaVersion => 2; // 更新版本号

  // 用户相关操作
  Future<List<User>> getAllUsers() => select(users).get();

  Future<User?> findUserById(int id) =>
      (select(users)..where((u) => u.id.equals(id))).getSingleOrNull();

  Future<int> insertUser(Insertable<User> user) => into(users).insert(user);

  Future<bool> updateUser(Insertable<User> user) => update(users).replace(user);

  Future<int> deleteUser(int id) =>
      (delete(users)..where((u) => u.id.equals(id))).go();

  // 项目相关操作
  Future<List<Project>> getAllProjects() => select(projects).get();

  Future<Project?> findProjectById(int id) =>
      (select(projects)..where((p) => p.id.equals(id))).getSingleOrNull();

  Future<int> insertProject(Insertable<Project> project) =>
      into(projects).insert(project);

  Future<bool> updateProject(Insertable<Project> project) =>
      update(projects).replace(project);

  Future<int> deleteProject(int id) =>
      (delete(projects)..where((p) => p.id.equals(id))).go();

  // 待办相关操作
  Future<List<Todo>> getAllTodos() => select(todos).get();

  Future<Todo?> findTodoById(int id) =>
      (select(todos)..where((t) => t.id.equals(id))).getSingleOrNull();

  Future<int> insertTodo(Insertable<Todo> todo) => into(todos).insert(todo);

  Future<bool> updateTodo(Insertable<Todo> todo) => update(todos).replace(todo);

  Future<int> deleteTodo(int id) =>
      (delete(todos)..where((t) => t.id.equals(id))).go();

  // 根据项目ID获取待办事项
  Future<List<Todo>> getTodosByProjectId(int projectId) {
    return (select(todos)..where((t) => t.projectId.equals(projectId))).get();
  }

  // 根据分配人ID获取待办事项
  Future<List<Todo>> getTodosByAssigneeId(int assigneeId) {
    return (select(todos)..where((t) => t.assigneeId.equals(assigneeId))).get();
  }

  // 笔记相关操作
  Future<List<Note>> getAllNotes() => select(notes).get();

  Future<Note?> findNoteById(int id) =>
      (select(notes)..where((n) => n.id.equals(id))).getSingleOrNull();

  Future<int> insertNote(Insertable<Note> note) => into(notes).insert(note);

  Future<bool> updateNote(Insertable<Note> note) => update(notes).replace(note);

  Future<int> deleteNote(int id) =>
      (delete(notes)..where((n) => n.id.equals(id))).go();

  // 根据项目ID获取笔记
  Future<List<Note>> getNotesByProjectId(int projectId) {
    return (select(notes)..where((n) => n.projectId.equals(projectId))).get();
  }

  // 根据待办ID获取笔记
  Future<List<Note>> getNotesByTodoId(int todoId) {
    return (select(notes)..where((n) => n.todoId.equals(todoId))).get();
  }

  // 搜索笔记
  Future<List<Note>> searchNotes(String keyword) {
    return (select(notes)
          ..where(
              (n) => n.title.like('%$keyword%') | n.content.like('%$keyword%')))
        .get();
  }
}

LazyDatabase _openConnection() {
  return LazyDatabase(() async {
    // 在所有平台上都使用文件数据库
    return DatabaseAdapterFactory.createConnection();
  });
}
